﻿using System;
using System.Linq;
using System.Text;
using Kaidou.Model;
using System.Data;
using System.Data.SqlClient;
using Kaidou.Util;
using System.Collections;
using Kaidou.Model;

/**
 * 受注見出のパーシスター
 */
namespace Kaidou.Persister
{
    public class TRN_Juchu_HPersister : Common
    {

        /**
        * 指定された受注ｂﾌデータモデルを取得する。
        * @param con コネクション
        * @param juchu_no 受注
        * @return TRN_Juchu_H データモデル
        */

        public TrnJuchuh getDetail(string juchu_no)
        {
            TrnJuchuh model = null;

            String sql =
                "select "
                    + " juchu_no, "
                    + " juchu_clss, "
                    + " juchu_date, "
                    + " union_code, "
                    + " branch_code, "
                    + " juchu_tanto_name, "
                    + " hope_date, "
                    + " hope_time_clss, "
                    + " total_seal_qty, "
                    + " ocr_order_qty, "
                    + " report_rate, "
                    + " fax_rcv_date, "
                    + " ocr_pass_txt, "
                    + " hachu_date, "
                    + " hachu_union_code, "
                    + " hachu_branch_code, "
                    + " deliv_date, "
                    + " deliv_tanto_name, "
                    + " deliv_comp_name, "
                    + " deliv_no, "
                    + " fax_error_flg, "
                    + " forced_juchu_flg, "
                    + " faxocr_text1, "
                    + " faxocr_text2, "
                    + " faxocr_text3, "
                    + " faxocr_text4, "
                    + " faxocr_text5, "
                    + " faxocr_text6, "
                    + " faxocr_text7, "
                    + " faxocr_text8, "
                    + " faxocr_text9, "
                    + " faxocr_text10, "
                    + " faxocr_text11, "
                    + " faxocr_text12, "
                    + " faxocr_text13, "
                    + " juchu_sts, "
                    + " reg_date, "
                    + " upd_date, "
                    + " upd_union_code, "
                    + " upd_branch_code, "
                    + " upd_func_id ,"
                    + " upd_num "
                    + " from trn_juchu_h "
                    + " where "
                    + " juchu_no = @juchu_no ";

            SqlDB db = new SqlDB();
            try
            {


                SqlParameter[] sqlParameter = { new SqlParameter("@juchu_no", SqlDbType.VarChar, 20) };
                sqlParameter[0].Value = juchu_no;
                SqlDataReader dataReader = db.GetDataCmd(sql.ToString(), sqlParameter);
                if (dataReader.Read())
                {
                    model = new TrnJuchuh();

                    model.JuchuNo = (string)dataReader["juchu_no"];
                    //model.setJuchu_no(rs.getString("juchu_no"));
                    model.JuchuClss = (string)dataReader["juchu_clss"];
                    //model.setJuchu_clss(rs.getString("juchu_clss"));
                    model.JuchuDate = (DateTime)dataReader["juchu_date"];
                    //model.setJuchu_date(rs.getTimestamp("juchu_date"));
                    model.UnionCode = (string)dataReader["union_code"];
                    //model.setUnion_code(rs.getString("union_code"));
                    model.BranchCode = (short)dataReader["branch_code"];
                    //model.setBranch_code(rs.getInt("branch_code"));
                    model.JuchuTantoName = (string)dataReader["juchu_tanto_name"];
                    //model.setJuchu_tanto_name(rs.getString("juchu_tanto_name"));
                    model.HopeDate = (DBNull.Value == dataReader["hope_date"]) ? null : (DateTime?)dataReader["hope_date"];
                    //model.setHope_date(rs.getDate("hope_date"));
                    model.HopeTimeClss = (string)dataReader["hope_time_clss"];
                    //model.setHope_time_clss(rs.getString("hope_time_clss"));
                    model.TotalSealQty = (int)dataReader["total_seal_qty"];
                    //model.setTotal_seal_qty(rs.getInt("total_seal_qty"));
                    model.OcrOrderQty = (int)dataReader["ocr_order_qty"];
                    //model.setOcr_order_qty(rs.getInt("ocr_order_qty"));
                    model.ReportRate = (float)dataReader["report_rate"];
                    //model.setReport_rate(rs.getFloat("report_rate"));
                    model.FaxRcvDate = (DBNull.Value == dataReader["fax_rcv_date"]) ? null : (DateTime?)dataReader["fax_rcv_date"];
                    //model.setFax_rcv_date(rs.getTimestamp("fax_rcv_date"));
                    model.OcrPassTxt = (string)dataReader["ocr_pass_txt"];
                    //model.setOcr_pass_txt(rs.getString("ocr_pass_txt"));
                    model.HachuDate = (DBNull.Value == dataReader["hachu_date"]) ? null : (DateTime?)dataReader["hachu_date"];
                    //model.setHachu_date(rs.getDate("hachu_date"));
                    model.HachuUnionCode = (string)dataReader["hachu_union_code"];
                    //model.setHachu_union_code(rs.getString("hachu_union_code"));
                    model.HachuBranchCode = (short)dataReader["hachu_branch_code"];
                    //model.setHachu_branch_code(rs.getInt("hachu_branch_code"));
                    model.DelivDate = (DBNull.Value == dataReader["deliv_date"]) ? null : (DateTime?)dataReader["deliv_date"];
                    //model.setDeliv_date(rs.getDate("deliv_date"));
                    model.DelivTantoName = (string)dataReader["deliv_tanto_name"];
                    //model.setDeliv_tanto_name(rs.getString("deliv_tanto_name"));
                    model.DelivCompName = (string)dataReader["deliv_comp_name"];
                    //model.setDeliv_comp_name(rs.getString("deliv_comp_name"));
                    model.DelivNo = (string)dataReader["deliv_no"];
                    //model.setDeliv_no(rs.getString("deliv_no"));
                    model.FaxErrorFlg = (string)dataReader["fax_error_flg"];
                    //model.setFax_error_flg(rs.getString("fax_error_flg"));
                    model.ForcedJuchuFlg = (string)dataReader["forced_juchu_flg"];
                    //model.setForced_juchu_flg(rs.getString("forced_juchu_flg"));
                    model.FaxocrText1 = (string)dataReader["faxocr_text1"];
                    model.FaxocrText2 = (string)dataReader["faxocr_text2"];
                    model.FaxocrText3 = (string)dataReader["faxocr_text3"];
                    model.FaxocrText4 = (string)dataReader["faxocr_text4"];
                    model.FaxocrText5 = (string)dataReader["faxocr_text5"];
                    model.FaxocrText6 = (string)dataReader["faxocr_text6"];
                    model.FaxocrText7 = (string)dataReader["faxocr_text7"];
                    model.FaxocrText8 = (string)dataReader["faxocr_text8"];
                    model.FaxocrText9 = (string)dataReader["faxocr_text9"];
                    model.FaxocrText10 = (string)dataReader["faxocr_text10"];
                    model.FaxocrText11 = (string)dataReader["faxocr_text11"];
                    model.FaxocrText12 = (string)dataReader["faxocr_text12"];
                    model.FaxocrText13 = (string)dataReader["faxocr_text13"];
                    model.JuchuSts = (string)dataReader["juchu_sts"];
                    //model.setJuchu_sts(rs.getString("juchu_sts"));
                    model.RegDate = (DateTime)dataReader["reg_date"];
                    //model.setReg_date(rs.getTimestamp("reg_date"));
                    model.UpdDate = (DateTime)dataReader["upd_date"];
                    //model.setUpd_date(rs.getTimestamp("upd_date"));
                    model.UpdUnionCode = (string)dataReader["upd_union_code"];
                    //model.setUpd_union_code(rs.getString("upd_union_code"));
                    model.UpdBranchCode = (short)dataReader["upd_branch_code"];
                    //model.setUpd_branch_code(rs.getInt("upd_branch_code"));
                    model.UpdFuncId = (string)dataReader["upd_func_id"];
                    //model.setUpd_func_id(rs.getString("upd_func_id"));
                    model.UpdNum = (short)dataReader["upd_num"];

                }


            }
            catch (Exception e)
            {
                LogManager.error("TRN_Juchu_HPersister - getDetail:" + e.Message + e.StackTrace);
            }
            finally
            {
                db.Close();
            }

            return model;

        }


        /**
         * 全ての受注見出のデータモデルを取得する。
         * @return ArrayList 全ての受注見出のデータモデルを格納したリスト
         */
        public ArrayList juchu_HGetAll()
        {

            TrnJuchuh model = null;
            ArrayList list = new ArrayList();

            String sql =
                "select "
                    + "juchu_no, "
                    + "juchu_clss, "
                    + "juchu_date, "
                    + "union_code, "
                    + "branch_code, "
                    + "juchu_tanto_name, "
                    + "hope_date, "
                    + "hope_time_clss, "
                    + "total_seal_qty, "
                    + "ocr_order_qty, "
                    + "report_rate, "
                    + "fax_rcv_date, "
                    + "ocr_pass_txt, "
                    + "hachu_date, "
                    + "hachu_union_code, "
                    + "hachu_branch_code, "
                    + "deliv_date, "
                    + "deliv_tanto_name, "
                    + "deliv_comp_name, "
                    + "deliv_no, "
                    + "fax_error_flg, "
                    + "forced_juchu_flg, "
                    + "faxocr_text1, "
                    + "faxocr_text2, "
                    + "faxocr_text3, "
                    + "faxocr_text4, "
                    + "faxocr_text5, "
                    + "faxocr_text6, "
                    + "faxocr_text7, "
                    + "faxocr_text8, "
                    + "faxocr_text9, "
                    + "faxocr_text10, "
                    + "faxocr_text11, "
                    + "faxocr_text12, "
                    + "faxocr_text13, "
                    + "juchu_sts, "
                    + "reg_date, "
                    + "upd_date, "
                    + "upd_union_code, "
                    + "upd_branch_code, "
                    + "upd_func_id ,"
                    + "upd_num ,"
                    + "from trn_juchu_h "
                    + "order by juchu_no ";

            SqlDB db = new SqlDB();

            try
            {

                SqlParameter[] sqlParameter = { };
                SqlDataReader dataReader = db.GetDataCmd(sql, sqlParameter);

                while (dataReader.Read())
                {

                    model = new TrnJuchuh();

                    model.JuchuNo = (string)dataReader["juchu_no"];
                    //model.setJuchu_no(rs.getString("juchu_no"));
                    model.JuchuClss = (string)dataReader["juchu_clss"];
                    //model.setJuchu_clss(rs.getString("juchu_clss"));
                    model.JuchuDate = (DateTime)dataReader["juchu_date"];
                    //model.setJuchu_date(rs.getTimestamp("juchu_date"));
                    model.UnionCode = (string)dataReader["union_code"];
                    //model.setUnion_code(rs.getString("union_code"));
                    model.BranchCode = (short)dataReader["branch_code"];
                    //model.setBranch_code(rs.getInt("branch_code"));
                    model.JuchuTantoName = (string)dataReader["juchu_tanto_name"];
                    //model.setJuchu_tanto_name(rs.getString("juchu_tanto_name"));
                    model.HopeDate = (DateTime)dataReader["hope_date"];
                    //model.setHope_date(rs.getDate("hope_date"));
                    model.HopeTimeClss = (string)dataReader["hope_time_clss"];
                    //model.setHope_time_clss(rs.getString("hope_time_clss"));
                    model.TotalSealQty = (int)dataReader["total_seal_qty"];
                    //model.setTotal_seal_qty(rs.getInt("total_seal_qty"));
                    model.OcrOrderQty = (int)dataReader["ocr_order_qty"];
                    //model.setOcr_order_qty(rs.getInt("ocr_order_qty"));
                    model.ReportRate = (float)dataReader["report_rate"];
                    //model.setReport_rate(rs.getFloat("report_rate"));
                    model.FaxRcvDate = (DateTime)dataReader["fax_rcv_date"];
                    //model.setFax_rcv_date(rs.getTimestamp("fax_rcv_date"));
                    model.OcrPassTxt = (string)dataReader["ocr_pass_txt"];
                    //model.setOcr_pass_txt(rs.getString("ocr_pass_txt"));
                    model.HachuDate = (DateTime)dataReader["hachu_date"];
                    //model.setHachu_date(rs.getDate("hachu_date"));
                    model.HachuUnionCode = (string)dataReader["hachu_union_code"];
                    //model.setHachu_union_code(rs.getString("hachu_union_code"));
                    model.HachuBranchCode = (short)dataReader["hachu_branch_code"];
                    //model.setHachu_branch_code(rs.getInt("hachu_branch_code"));
                    model.DelivDate = (DateTime)dataReader["deliv_date"];
                    //model.setDeliv_date(rs.getDate("deliv_date"));
                    model.DelivTantoName = (string)dataReader["deliv_tanto_name"];
                    //model.setDeliv_tanto_name(rs.getString("deliv_tanto_name"));
                    model.DelivCompName = (string)dataReader["deliv_comp_name"];
                    //model.setDeliv_comp_name(rs.getString("deliv_comp_name"));
                    model.DelivNo = (string)dataReader["deliv_no"];
                    //model.setDeliv_no(rs.getString("deliv_no"));
                    model.FaxErrorFlg = (string)dataReader["fax_error_flg"];
                    //model.setFax_error_flg(rs.getString("fax_error_flg"));
                    model.ForcedJuchuFlg = (string)dataReader["forced_juchu_flg"];
                    //model.setForced_juchu_flg(rs.getString("forced_juchu_flg"));
                    model.FaxocrText1 = (string)dataReader["faxocr_text1"];
                    model.FaxocrText2 = (string)dataReader["faxocr_text2"];
                    model.FaxocrText3 = (string)dataReader["faxocr_text3"];
                    model.FaxocrText4 = (string)dataReader["faxocr_text4"];
                    model.FaxocrText5 = (string)dataReader["faxocr_text5"];
                    model.FaxocrText6 = (string)dataReader["faxocr_text6"];
                    model.FaxocrText7 = (string)dataReader["faxocr_text7"];
                    model.FaxocrText8 = (string)dataReader["faxocr_text8"];
                    model.FaxocrText9 = (string)dataReader["faxocr_text9"];
                    model.FaxocrText10 = (string)dataReader["faxocr_text10"];
                    model.FaxocrText11 = (string)dataReader["faxocr_text11"];
                    model.FaxocrText12 = (string)dataReader["faxocr_text12"];
                    model.FaxocrText13 = (string)dataReader["faxocr_text13"];
                    model.JuchuSts = (string)dataReader["juchu_sts"];
                    //model.setJuchu_sts(rs.getString("juchu_sts"));
                    model.RegDate = (DateTime)dataReader["reg_date"];
                    //model.setReg_date(rs.getTimestamp("reg_date"));
                    model.UpdDate = (DateTime)dataReader["upd_date"];
                    //model.setUpd_date(rs.getTimestamp("upd_date"));
                    model.UpdUnionCode = (string)dataReader["upd_union_code"];
                    //model.setUpd_union_code(rs.getString("upd_union_code"));
                    model.UpdBranchCode = (short)dataReader["upd_branch_code"];
                    //model.setUpd_branch_code(rs.getInt("upd_branch_code"));
                    model.UpdFuncId = (string)dataReader["upd_func_id"];
                    //model.setUpd_func_id(rs.getString("upd_func_id"));
                    model.UpdNum = (short)dataReader["upd_num"];

                    list.Add(model);
                }
                

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Juchu_HPersister - juchu_HGetAll:" + e.Message);
            }
            finally
            {
                db.Close();
            }

            return list;

        }

        /**
         * 受注見出を更新する
         * @param model 受注見出のデータモデル
         */
        public void juchu_HUpdate(TrnJuchuh M)
        {
            String sql =
                "update trn_juchu_h set "
                    + "juchu_clss = @juchu_clss, "
                    + "juchu_date = @juchu_date, "
                    + "union_code = @union_code, "
                    + "branch_code = @branch_code, "
                    + "juchu_tanto_name = @juchu_tanto_name, "
                    + "hope_date = @hope_date, "
                    + "hope_time_clss = @hope_time_clss, "
                    + "total_seal_qty = @total_seal_qty, "
                    + "ocr_order_qty = @ocr_order_qty, "
                    + "report_rate = @report_rate, "
                    + "fax_rcv_date = @fax_rcv_date, "
                    + "ocr_pass_txt = @ocr_pass_txt, "
                    + "hachu_date = @hachu_date, "
                    + "hachu_union_code = @hachu_union_code, "
                    + "hachu_branch_code = @hachu_branch_code, "
                    + "deliv_date = @deliv_date, "
                    + "deliv_tanto_name = @deliv_tanto_name, "
                    + "deliv_comp_name = @deliv_comp_name, "
                    + "deliv_no = @deliv_no, "
                    + "fax_error_flg = @fax_error_flg, "
                    + "forced_juchu_flg = @forced_juchu_flg, "
                    + "faxocr_text1 = @faxocr_text1, "
                    + "faxocr_text2 = @faxocr_text2, "
                    + "faxocr_text3 = @faxocr_text3, "
                    + "faxocr_text4 = @faxocr_text4, "
                    + "faxocr_text5 = @faxocr_text5, "
                    + "faxocr_text6 = @faxocr_text6, "
                    + "faxocr_text7 = @faxocr_text7, "
                    + "faxocr_text8 = @faxocr_text8, "
                    + "faxocr_text9 = @faxocr_text9, "
                    + "faxocr_text10 = @faxocr_text10, "
                    + "faxocr_text11 = @faxocr_text11, "
                    + "faxocr_text12 = @faxocr_text12, "
                    + "faxocr_text13 = @faxocr_text13, "
                    + "juchu_sts = @juchu_sts, "
                    + "reg_date = @reg_date, "
                    + "upd_date = @upd_date, "
                    + "upd_union_code = @upd_union_code, "
                    + "upd_branch_code = @upd_branch_code, "
                    + "upd_func_id = @upd_func_id, "
                    + "upd_num = @upd_num"
                    + "where "
                    + "juchu_no = @juchu_no";
            SqlDB db = new SqlDB();
            try
            {


                SqlParameter[] sqlParameter = {   
                                                    new SqlParameter("@juchu_clss",SqlDbType.VarChar,2),
                                                    new SqlParameter("@juchu_date",SqlDbType.DateTime),
                                                    new SqlParameter("@union_code",SqlDbType.VarChar,5),
                                                    new SqlParameter("@branch_code",SqlDbType.SmallInt),
                                                    new SqlParameter("@juchu_tanto_name",SqlDbType.VarChar,20),
                                                    new SqlParameter("@hope_date",SqlDbType.DateTime),
                                                    new SqlParameter("@hope_time_clss",SqlDbType.VarChar,1),
                                                    new SqlParameter("@total_seal_qty",SqlDbType.Int),
                                                    new SqlParameter("@ocr_order_qty",SqlDbType.Int),
                                                    new SqlParameter("@report_rate",SqlDbType.Real),
                                                    new SqlParameter("@fax_rcv_date",SqlDbType.DateTime),
                                                    new SqlParameter("@ocr_pass_txt",SqlDbType.VarChar,60),
                                                    new SqlParameter("@hachu_date",SqlDbType.DateTime),
                                                    new SqlParameter("@hachu_union_code",SqlDbType.VarChar,5),
                                                    new SqlParameter("@hachu_branch_code",SqlDbType.SmallInt),
                                                    new SqlParameter("@deliv_date",SqlDbType.DateTime),
                                                    new SqlParameter("@deliv_tanto_name",SqlDbType.VarChar,20),
                                                    new SqlParameter("@deliv_comp_name",SqlDbType.VarChar,30),
                                                    new SqlParameter("@deliv_no",SqlDbType.VarChar,15),
                                                    new SqlParameter("@fax_error_flg",SqlDbType.VarChar,1),
                                                    new SqlParameter("@forced_juchu_flg",SqlDbType.VarChar,1),
                                                    new SqlParameter("@faxocr_text1",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text2",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text3",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text4",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text5",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text6",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text7",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text8",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text9",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text10",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text11",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text12",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text13",SqlDbType.VarChar,100),
                                                    new SqlParameter("@juchu_sts",SqlDbType.VarChar,2),
                                                    new SqlParameter("@reg_date",SqlDbType.DateTime),
                                                    new SqlParameter("@upd_date",SqlDbType.VarChar,20),
                                                    new SqlParameter("@upd_union_code",SqlDbType.VarChar,5),
                                                    new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),
                                                    new SqlParameter("@upd_func_id",SqlDbType.VarChar,20),
                                                    new SqlParameter("@upd_num",SqlDbType.SmallInt),
                                                    new SqlParameter("@juchu_no",SqlDbType.VarChar,20)
                                                  };

                sqlParameter[0].Value = M.JuchuClss;
                sqlParameter[1].Value = M.JuchuDate;
                sqlParameter[2].Value = M.UnionCode;
                sqlParameter[3].Value = M.BranchCode;
                sqlParameter[4].Value = M.JuchuTantoName;
                sqlParameter[5].Value = M.HopeDate;
                sqlParameter[6].Value = M.HopeTimeClss;
                sqlParameter[7].Value = M.TotalSealQty;
                sqlParameter[8].Value = M.OcrOrderQty;
                sqlParameter[9].Value = M.ReportRate;
                sqlParameter[10].Value = M.FaxRcvDate;
                sqlParameter[11].Value = M.OcrPassTxt;
                sqlParameter[12].Value = M.HachuDate;
                sqlParameter[13].Value = M.HachuUnionCode;
                sqlParameter[14].Value = M.HachuBranchCode;
                sqlParameter[15].Value = M.DelivDate;
                sqlParameter[16].Value = M.DelivTantoName;
                sqlParameter[17].Value = M.DelivCompName;
                sqlParameter[18].Value = M.DelivNo;
                sqlParameter[19].Value = M.FaxErrorFlg;
                sqlParameter[20].Value = M.ForcedJuchuFlg;
                sqlParameter[21].Value = M.FaxocrText1;
                sqlParameter[22].Value = M.FaxocrText2;
                sqlParameter[23].Value = M.FaxocrText3;
                sqlParameter[24].Value = M.FaxocrText4;
                sqlParameter[25].Value = M.FaxocrText5;
                sqlParameter[26].Value = M.FaxocrText6;
                sqlParameter[27].Value = M.FaxocrText7;
                sqlParameter[28].Value = M.FaxocrText8;
                sqlParameter[29].Value = M.FaxocrText9;
                sqlParameter[30].Value = M.FaxocrText10;
                sqlParameter[31].Value = M.FaxocrText11;
                sqlParameter[32].Value = M.FaxocrText12;
                sqlParameter[33].Value = M.FaxocrText13;
                sqlParameter[34].Value = M.JuchuClss;
                sqlParameter[35].Value = M.RegDate;
                sqlParameter[36].Value = M.UpdDate;
                sqlParameter[37].Value = M.UpdUnionCode;
                sqlParameter[38].Value = M.UpdBranchCode;
                sqlParameter[39].Value = M.UpdFuncId;
                sqlParameter[40].Value = M.UpdNum;
                sqlParameter[41].Value = M.JuchuNo;


                db.ExecuteCmd(sql, sqlParameter);


            }
            catch (Exception e)
            {
                LogManager.error("TRN_Juchu_HPersister - juchu_HUpdate:" + e.Message);
            }
            finally
            {
                db.Close();

            }

        }

        /**
	     * 受注見出を登録する
	     * @param model 受注見出のデータモデル
	     */
        public void juchu_HInsert(SqlDB db,TrnJuchuh M)
        {
            String sql =
                "insert into trn_juchu_h ( "
                    + "juchu_no, "
                    + "juchu_clss, "
                    + "juchu_date, "
                    + "union_code, "
                    + "branch_code, "
                    + "juchu_tanto_name, "
                    + "hope_date, "
                    + "hope_time_clss, "
                    + "total_seal_qty, "
                    + "ocr_order_qty, "
                    + "report_rate, "
                    + "fax_rcv_date, "
                    + "ocr_pass_txt, "
                    + "hachu_date, "
                    + "hachu_union_code, "
                    + "hachu_branch_code, "
                    + "deliv_date, "
                    + "deliv_tanto_name, "
                    + "deliv_comp_name, "
                    + "deliv_no, "
                    + "fax_error_flg, "
                    + "forced_juchu_flg, "
                    + "faxocr_text1, "
                    + "faxocr_text2, "
                    + "faxocr_text3, "
                    + "faxocr_text4, "
                    + "faxocr_text5, "
                    + "faxocr_text6, "
                    + "faxocr_text7, "
                    + "faxocr_text8, "
                    + "faxocr_text9, "
                    + "faxocr_text10, "
                    + "faxocr_text11, "
                    + "faxocr_text12, "
                    + "faxocr_text13, "
                    + "juchu_sts, "
                    + "reg_date, "
                    + "upd_date, "
                    + "upd_union_code, "
                    + "upd_branch_code, "
                    + "upd_func_id, "
                    + "upd_num "
                    + ") values ( "
                    + "@juchu_no, "
                    + "@juchu_clss, "
                    + "@juchu_date, "
                    + "@union_code, "
                    + "@branch_code, "
                    + "@juchu_tanto_name, "
                    + "@hope_date, "
                    + "@hope_time_clss, "
                    + "@total_seal_qty, "
                    + "@ocr_order_qty, "
                    + "@report_rate, "
                    + "@fax_rcv_date, "
                    + "@ocr_pass_txt, "
                    + "@hachu_date, "
                    + "@hachu_union_code, "
                    + "@hachu_branch_code, "
                    + "@deliv_date, "
                    + "@deliv_tanto_name, "
                    + "@deliv_comp_name, "
                    + "@deliv_no, "
                    + "@fax_error_flg, "
                    + "@forced_juchu_flg, "
                    + "@faxocr_text1, "
                    + "@faxocr_text2, "
                    + "@faxocr_text3, "
                    + "@faxocr_text4, "
                    + "@faxocr_text5, "
                    + "@faxocr_text6, "
                    + "@faxocr_text7, "
                    + "@faxocr_text8, "
                    + "@faxocr_text9, "
                    + "@faxocr_text10, "
                    + "@faxocr_text11, "
                    + "@faxocr_text12, "
                    + "@faxocr_text13, "
                    + "@juchu_sts, "
                    + "@reg_date, "
                    + "@upd_date, "
                    + "@upd_union_code, "
                    + "@upd_branch_code, "
                    + "@upd_func_id, "
                    + "@upd_num )";
            try
            {
                LogManager.info("TRN_Juchu_HPersister - juchu_HInsert - begin");
                //SqlDB db = new SqlDB();
                SqlParameter[] sqlParameter = {   
                                                    new SqlParameter("@juchu_clss",SqlDbType.VarChar,2),
                                                    new SqlParameter("@juchu_date",SqlDbType.DateTime),
                                                    new SqlParameter("@union_code",SqlDbType.VarChar,5),
                                                    new SqlParameter("@branch_code",SqlDbType.SmallInt),
                                                    new SqlParameter("@juchu_tanto_name",SqlDbType.VarChar,20),
                                                    new SqlParameter("@hope_date",SqlDbType.DateTime),
                                                    new SqlParameter("@hope_time_clss",SqlDbType.VarChar,1),
                                                    new SqlParameter("@total_seal_qty",SqlDbType.Int),
                                                    new SqlParameter("@ocr_order_qty",SqlDbType.Int),
                                                    new SqlParameter("@report_rate",SqlDbType.Real),
                                                    new SqlParameter("@fax_rcv_date",SqlDbType.DateTime),
                                                    new SqlParameter("@ocr_pass_txt",SqlDbType.VarChar,60),
                                                    new SqlParameter("@hachu_date",SqlDbType.DateTime),
                                                    new SqlParameter("@hachu_union_code",SqlDbType.VarChar,5),
                                                    new SqlParameter("@hachu_branch_code",SqlDbType.SmallInt),
                                                    new SqlParameter("@deliv_date",SqlDbType.DateTime),
                                                    new SqlParameter("@deliv_tanto_name",SqlDbType.VarChar,20),
                                                    new SqlParameter("@deliv_comp_name",SqlDbType.VarChar,30),
                                                    new SqlParameter("@deliv_no",SqlDbType.VarChar,15),
                                                    new SqlParameter("@fax_error_flg",SqlDbType.VarChar,1),
                                                    new SqlParameter("@forced_juchu_flg",SqlDbType.VarChar,1),
                                                    new SqlParameter("@faxocr_text1",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text2",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text3",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text4",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text5",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text6",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text7",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text8",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text9",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text10",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text11",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text12",SqlDbType.VarChar,100),
                                                    new SqlParameter("@faxocr_text13",SqlDbType.VarChar,100),
                                                    new SqlParameter("@juchu_sts",SqlDbType.VarChar,2),
                                                    new SqlParameter("@reg_date",SqlDbType.DateTime),
                                                    new SqlParameter("@upd_date",SqlDbType.VarChar,20),
                                                    new SqlParameter("@upd_union_code",SqlDbType.VarChar,5),
                                                    new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),
                                                    new SqlParameter("@upd_func_id",SqlDbType.VarChar,20),
                                                    new SqlParameter("@upd_num",SqlDbType.SmallInt),
                                                    new SqlParameter("@juchu_no",SqlDbType.VarChar,20)
                                                  };
                DateTime now = System.DateTime.Now;
                sqlParameter[0].Value = M.JuchuClss==null?"":M.JuchuClss;
                sqlParameter[1].Value = M.JuchuDate==null?now:M.JuchuDate;
                LogManager.info("TRN_Juchu_HPersister - juchu_HInsert - M.JuchuDate :"+M.JuchuDate.ToString());
                sqlParameter[2].Value = M.UnionCode;
                sqlParameter[3].Value = M.BranchCode;
                sqlParameter[4].Value = M.JuchuTantoName;
                if(M.HopeDate == null){
                     sqlParameter[5].Value = DBNull.Value;
                }else{
                    sqlParameter[5].Value = M.HopeDate;
                }
                LogManager.info("TRN_Juchu_HPersister - juchu_HInsert - M.HopeDate :" + M.HopeDate.ToString());
                sqlParameter[6].Value = M.HopeTimeClss;
                sqlParameter[7].Value = M.TotalSealQty;
                sqlParameter[8].Value = M.OcrOrderQty;
                sqlParameter[9].Value = M.ReportRate;
                LogManager.info("-------------------------------------------------------TRN_Juchu_HPersister - juchu_HInsert - M.ReportRate :" + M.ReportRate.ToString());
                if (M.FaxRcvDate == null)
                {
                    sqlParameter[10].Value = DBNull.Value;
                }
                else
                {
                    sqlParameter[10].Value = M.FaxRcvDate;
                }
                LogManager.info("TRN_Juchu_HPersister - juchu_HInsert - M.FaxRcvDate :" + M.FaxRcvDate.ToString());
                sqlParameter[11].Value = M.OcrPassTxt==null?"":M.OcrPassTxt;
                if (M.HachuDate == null)
                {
                    sqlParameter[12].Value =DBNull.Value ;
                }
                else
                {
                    sqlParameter[12].Value = M.HachuDate;
                }
                
                LogManager.info("TRN_Juchu_HPersister - juchu_HInsert - M.HachuDate :" + M.HachuDate.ToString());
                sqlParameter[13].Value = M.HachuUnionCode==null?"":M.HachuUnionCode;
                sqlParameter[14].Value = M.HachuBranchCode;
                if (M.DelivDate == null)
                {
                    sqlParameter[15].Value = DBNull.Value ;
                }
                else
                {
                    sqlParameter[15].Value = M.DelivDate;
                }
                
                LogManager.info("TRN_Juchu_HPersister - juchu_HInsert - M.DelivDate :" + M.DelivDate.ToString());
                sqlParameter[16].Value = M.DelivTantoName == null ? "" : M.DelivTantoName;
                sqlParameter[17].Value = M.DelivCompName == null ? "" : M.DelivCompName;
                sqlParameter[18].Value = M.DelivNo == null ? "" : M.DelivNo;
                sqlParameter[19].Value = M.FaxErrorFlg == null ? "" : M.FaxErrorFlg;
                sqlParameter[20].Value = M.ForcedJuchuFlg == null ? "" : M.ForcedJuchuFlg;
                sqlParameter[21].Value = M.FaxocrText1 == null ? "" : M.FaxocrText1;
                sqlParameter[22].Value = M.FaxocrText2 == null ? "" : M.FaxocrText2;
                sqlParameter[23].Value = M.FaxocrText3 == null ? "" : M.FaxocrText3;
                sqlParameter[24].Value = M.FaxocrText4 == null ? "" : M.FaxocrText4;
                sqlParameter[25].Value = M.FaxocrText5 == null ? "" : M.FaxocrText5;
                sqlParameter[26].Value = M.FaxocrText6 == null ? "" : M.FaxocrText6;
                sqlParameter[27].Value = M.FaxocrText7 == null ? "" : M.FaxocrText7;
                sqlParameter[28].Value = M.FaxocrText8 == null ? "" : M.FaxocrText8;
                sqlParameter[29].Value = M.FaxocrText9 == null ? "" : M.FaxocrText9;
                sqlParameter[30].Value = M.FaxocrText10 == null ? "" : M.FaxocrText10;
                sqlParameter[31].Value = M.FaxocrText11 == null ? "" : M.FaxocrText11;
                sqlParameter[32].Value = M.FaxocrText12 == null ? "" : M.FaxocrText12;
                sqlParameter[33].Value = M.FaxocrText13 == null ? "" : M.FaxocrText13;
                sqlParameter[34].Value = M.JuchuSts == null ? "" : M.JuchuSts; 
                sqlParameter[35].Value = M.RegDate == null ? now : M.RegDate;
                LogManager.info("TRN_Juchu_HPersister - juchu_HInsert - M.RegDate :" + M.RegDate.ToString());
                sqlParameter[36].Value = M.UpdDate == null ? now : M.UpdDate;
                LogManager.info("TRN_Juchu_HPersister - juchu_HInsert - M.UpdDate :" + M.UpdDate.ToString());
                sqlParameter[37].Value = M.UpdUnionCode == null ? "" : M.UpdUnionCode; 
                sqlParameter[38].Value = M.UpdBranchCode;
                sqlParameter[39].Value = M.UpdFuncId == null ? "" : M.UpdFuncId; 
                sqlParameter[40].Value = M.UpdNum;
                sqlParameter[41].Value = M.JuchuNo == null ? "" : M.JuchuNo; 


                db.ExecuteCmd(sql, sqlParameter);
                //db.Close();

                LogManager.info("TRN_Juchu_HPersister - juchu_HInsert - after");

            }
            catch (Exception exe)
            {
                LogManager.error("TRN_Juchu_HPersister - juchu_HInsert:" + exe.Message+exe.StackTrace);
                throw new Exception("TRN_Juchu_HPersister juchu_hinsert error");
            }


        }

        /**
	     * 渡された注文検索モデルの条件による注文検索を行う。
	     * @param con コネクション
	     * @param model 注文検索モデル
	     * @return Vector 検索されたモデルのリスト
	     * @throws PersisterSQLException
	    */
        public Pager search(OrderSearchModel model, int pageNo, int pageSize) 
        {
            
		    LogManager.info("TRN_Juchu_HPersister search - union_code : " + model.getUnion_code());
		    LogManager.info("TRN_Juchu_HPersister search - office_name : " + model.getOffice_name());
		    LogManager.info("TRN_Juchu_HPersister search - office_kana : " + model.getOffice_kana());
		    LogManager.info("TRN_Juchu_HPersister search - branch_name : " + model.getBranch_name());
		    LogManager.info("TRN_Juchu_HPersister search - juchu_sts : " + model.getJuchu_sts());
		    LogManager.info("TRN_Juchu_HPersister search - juchu_clss : " + model.getJuchu_clss());
		    LogManager.info("TRN_Juchu_HPersister search - fax_error_flg : " + model.getFax_error_flg());
		    LogManager.info("TRN_Juchu_HPersister search - juchu_str_rate : " + model.getReport_str_rate().ToString());
		    LogManager.info("TRN_Juchu_HPersister search - juchu_end_rate : " + model.getReport_end_rate().ToString());
		    LogManager.info("TRN_Juchu_HPersister search - ocr_order_qty : " + model.getOcr_order_qty().ToString());
		    LogManager.info("TRN_Juchu_HPersister search - juchu_no : " + model.getJuchu_no());
            LogManager.info("TRN_Juchu_HPersister search - branch_code : " + model.getBranch_code().ToString() );

		    ArrayList list = new ArrayList();
		    string sql = null;
            SqlDB db = new SqlDB();
            try
            {
                if (StringUtil.isEmpty(model.getUnion_code()) && StringUtil.isEmpty(model.getOffice_name()) && StringUtil.isEmpty(model.getOffice_kana()) && StringUtil.isEmpty(model.getBranch_name()) && model.getBranch_code() < 0)
                {
                    //Union系が全く検索条件にない。

                    sql =
                        "select  r.juchu_date as juchu_date, "
                            + "  r.juchu_date as juchu_date_order, "
                            + " r.union_code as union_code,"
                            + " r.branch_code as branch_code,"
                            + " r.juchu_clss as juchu_clss,"
                            + " r.juchu_no as juchu_no,"
                            + " r.total_seal_qty as total_seal_qty,"
                            + " r.juchu_sts as juchu_sts,"
                            + " r.fax_error_flg as fax_error_flg ,"
                            + " r.report_rate as report_rate ,"
                            + " r.ocr_order_qty as ocr_order_qty "

                            + " from "
                            + " trn_juchu_h as r "
                            + " where "
                            + " r.juchu_sts != '" + TrnJuchuh.JUCHU_STATUS_DELETE + "'";

                }
                else
                {
                    if (model.getBranch_code() > 0)
                    {
                        sql =
                            "select  r.juchu_date as juchu_date, "
                                + "  r.juchu_date as juchu_date_order, "
                                + " r.union_code as union_code,"
                                + " r.branch_code as branch_code,"
                                + " r.juchu_clss as juchu_clss,"
                                + " r.juchu_no as juchu_no,"
                                + " r.total_seal_qty as total_seal_qty,"
                                + " r.juchu_sts as juchu_sts,"
                                + " r.fax_error_flg as fax_error_flg ,"
                                + " r.report_rate as report_rate ,"
                                + " r.ocr_order_qty as ocr_order_qty "

                                + " from "
                                + " trn_juchu_h as r, "
                                + " mst_union_branch as u, "
                                + " mst_union as k "
                                + " where "
                                + " r.union_code = u.union_code "
                                + " and r.union_code = k.union_code "
                                + " and r.branch_code = u.branch_code "
                                + " and r.juchu_sts != '" + TrnJuchuh.JUCHU_STATUS_DELETE + "'";
                    }

                    else if (model.getBranch_code() == 0)
                    {
                        sql =
                            "select  r.juchu_date as juchu_date, "
                                + "  r.juchu_date as juchu_date_order, "
                                + " r.union_code as union_code,"
                                + " r.branch_code as branch_code,"
                                + " r.juchu_clss as juchu_clss,"
                                + " r.juchu_no as juchu_no,"
                                + " r.total_seal_qty as total_seal_qty,"
                                + " r.juchu_sts as juchu_sts,"
                                + " r.fax_error_flg as fax_error_flg ,"
                                + " r.report_rate as report_rate ,"
                                + " r.ocr_order_qty as ocr_order_qty "

                                + " from "
                                + " trn_juchu_h as r, "
                                + " mst_union as k "
                                + " where "
                                + "   r.union_code = k.union_code "
                                + " and r.juchu_sts != '" + TrnJuchuh.JUCHU_STATUS_DELETE + "'";

                    }
                    else
                    {

                        sql =
                             "select  r.juchu_date as juchu_date, "
                                 + "  r.juchu_date as juchu_date_order, "
                                 + " r.union_code as union_code,"
                                 + " r.branch_code as branch_code,"
                                 + " r.juchu_clss as juchu_clss,"
                                 + " r.juchu_no as juchu_no,"
                                 + " r.total_seal_qty as total_seal_qty,"
                                 + " r.juchu_sts as juchu_sts,"
                                 + " r.fax_error_flg as fax_error_flg ,"
                                 + " r.report_rate as report_rate ,"
                                 + " r.ocr_order_qty as ocr_order_qty "

                                 + " from "
                                 + " trn_juchu_h as r, "
                                 + " mst_union_view as u, "
                                 + " mst_union as k "
                                 + " where "
                                 + " r.union_code = u.union_code "
                                 + " and r.union_code = k.union_code "
                                 + " and r.branch_code = u.branch_code "
                                 + " and r.juchu_sts != '" + TrnJuchuh.JUCHU_STATUS_DELETE + "'";

                    }

                }

                //条件 Juchu_str_date
                if (model.getJuchu_str_date() != null)
                {
                    sql += " and CONVERT(varchar(20),r.juchu_date,11) >= '" + model.getJuchu_str_dateAsDate().ToString("yy/MM/dd") + "'";
                }
                //条件 Juchu_str_date
                if (model.getJuchu_end_date() != null)
                {
                    sql += " and CONVERT(varchar(20),r.juchu_date,11) <= '" + model.getJuchu_end_dateAsDate().ToString("yy/MM/dd") + "'";
                }
                //条件 Union_code
                if (model.getUnion_code() != null)
                {
                    sql += " and r.union_code = '" + model.getUnion_code() + "'";
                }
                //条件 組合員名
                if (model.getOffice_name() != null)
                {
                    sql += " and k.office_name like '%" + model.getOffice_name() + "%'";
                }
                //条件 組合員カナ
                if (model.getOffice_kana() != null)
                {
                    sql += " and k.office_kana like '%" + model.getOffice_kana() + "%'";
                }
                //条件 支店名
                if (model.getBranch_name() != null)
                {
                    sql += " and u.office_name like '%" + model.getBranch_name() + "%'";
                }
                //条件 状態
                if (model.getJuchu_sts() != null)
                {
                    sql += " and r.juchu_sts = '" + model.getJuchu_sts() + "'";
                }
                //条件 報告区分
                if (model.getJuchu_clss() != null)
                {
                    sql += " and r.juchu_clss = '" + model.getJuchu_clss() + "'";
                }
                //条件 エラー
                if (model.getFax_error_flg() != null)
                {
                    sql += " and r.fax_error_flg = '" + model.getFax_error_flg() + "'";
                }
                //報告率(開始）
                if (model.getReport_str_rate() > 0)
                {
                    sql += " and r.report_rate > " + model.getReport_str_rate();
                }
                //報告率(終了）
                if (model.getReport_end_rate() > 0)
                {
                    sql += " and r.report_rate < " + model.getReport_end_rate();
                }
                //貼付報告書注文
                if (model.getOcr_order_qty() > 0)
                {
                    sql += " and r.ocr_order_qty >= " + model.getOcr_order_qty();
                }
                //条件 報告番号
                if (model.getJuchu_no() != null)
                {
                    sql += " and r.juchu_no = '" + model.getJuchu_no() + "'";
                }
                // branch_code
                if (model.getBranch_code() >= 0)
                {
                    sql += " and r.branch_code = " + model.getBranch_code() + "";
                }

                sql += " order by juchu_date_order desc,r.juchu_no desc ";

                LogManager.info("TRN_Juchu_HPersister search order query : " + sql);


                SqlParameter[] sqlParameter = { };
                SqlDataReader dataReader = db.GetDataCmd(sql, sqlParameter);

                while (dataReader.Read())
                {
                    OrderSearchModel juchu = new OrderSearchModel();

                    juchu.setJuchu_date((DateTime)dataReader["juchu_date"]);
                    juchu.setUnion_code((string)dataReader["union_code"]);
                    juchu.setBranch_code((short)dataReader["branch_code"]);
                    juchu.setJuchu_clss((string)dataReader["juchu_clss"]);
                    juchu.setJuchu_no((string)dataReader["juchu_no"]);
                    juchu.setTotal_seal_qty((int)dataReader["total_seal_qty"]);
                    juchu.setJuchu_sts((string)dataReader["juchu_sts"]);
                    juchu.setFax_error_flg((string)dataReader["fax_error_flg"]);
                    juchu.setReport_rate((float)dataReader["report_rate"]);
                    juchu.setOcr_order_qty((int)dataReader["ocr_order_qty"]);
                    juchu.setTotal_seal_qty((int)dataReader["total_seal_qty"]);
                    list.Add(juchu);
                }


            }
            catch (Exception e)
            {
                LogManager.error("TRN_Juchu_H_Persister search " + e.Message + e.StackTrace);
            }
            finally
            {
                db.Close();
            }

            if (pageNo == 0)
            {
                pageNo = 1;
            }

            Pager pager = new Pager();
            pager.PageNo = pageNo;
            pager.PageSize = pageSize;
            pager.Total = list.Count;
            pager.Pages = pager.Total % pageSize == 0 ? pager.Total / pageSize : pager.Total / pageSize + 1;
            ArrayList result = new ArrayList();
            object[] bsm = list.ToArray();
            for (int i = (pageNo - 1) * pageSize; i < ((((pageNo - 1) * pageSize + pageSize)) >= bsm.Length ? bsm.Length : ((pageNo - 1) * pageSize + pageSize)); i++)
            {
                result.Add((OrderSearchModel)bsm[i]);

            }
            pager.List = result;

            return pager;
	    }

        /**
	     * ２００３年１月１日〜ログイン日までのデータで、合計枚数を集計し取得する。
	     * @update 2004/01/07 T.Sakai 累計の起算日を変更
	     */
        /**
         * 【廃止】 本日１月１日〜ログイン日までのデータで、合計枚数を集計し取得する。
         * @param con コネクション
         * @param union_code 組合員コード
         * @return int 合計枚数
         */
        public int getTotalSeal(String union_code)
        {
            LogManager.info("TRN_Juchu_HPersister - getTotalSeal - in");
            LogManager.info("TRN_Juchu_HPersister - param - union_code : " + union_code);
            int total = 0;
            String sql = "select sum(total_seal_qty) from trn_juchu_h " + " where union_code=@union_code " + " and juchu_sts in ( @a , @b )";
            SqlDB db = new SqlDB();
            try
            {


                SqlParameter[] sqlParameter = { new SqlParameter("@union_code",SqlDbType.VarChar,5),
                                                new SqlParameter("@a",SqlDbType.VarChar,2),
                                                new SqlParameter("@b",SqlDbType.VarChar,2)
                                              };
                sqlParameter[0].Value = union_code;
                sqlParameter[1].Value = TrnJuchuh.JUCHU_STATUS_ORDERD;
                sqlParameter[2].Value = TrnJuchuh.JUCHU_STATUS_DELIVERD;

                SqlDataReader dataReader = db.GetDataCmd(sql, sqlParameter);
                if (dataReader.Read())
                {
                    total = (int)dataReader[0];
                }
                

                LogManager.info("TRN_Juchu_HPersister - result - total : " + total);

            }
            catch (Exception e)
            {
                LogManager.info("TRN_Juchu_HPersister - getTotalSeal : " + e.Message);
            }
            finally
            {
                db.Close();
            }

            return total;

        }

        /**
	     * 1/1〜searchDate日までのデータで、合計枚数を集計し取得する。
	     * @param con コネクション
	     * @param union_code 組合員コード
	     * @param searchDate データ取得範囲終了日
	     * @return int 合計枚数
	     * @throws PersisterSQLException
	    */
        public int getTotalSeal(String union_code, DateTime searchDate)
        {
            LogManager.info("TRN_Juchu_HPersister - getTotalSeal - in");
            LogManager.info("TRN_Juchu_HPersister - param - union_code : " + union_code + " searchDate [" + searchDate + "]");
            int total = 0;
            String sql = "select sum(total_seal_qty) from trn_juchu_h " + " where YEAR( reg_date ) = @year " + " and CONVERT(varchar(30),reg_date,11) <= @date " + " and union_code = @union_code " + " and juchu_sts in ( @a ,@b )";
            SqlDB db = new SqlDB();
            try
            {


                SqlParameter[] sqlParameter = { new SqlParameter("@union_code",SqlDbType.VarChar,5),
                                                new SqlParameter("@a",SqlDbType.VarChar,2),
                                                new SqlParameter("@b",SqlDbType.VarChar,2),
                                                new SqlParameter("@year",SqlDbType.VarChar),
                                                new SqlParameter("@date",SqlDbType.VarChar,30),
                                              };
                sqlParameter[0].Value = union_code;
                sqlParameter[1].Value = TrnJuchuh.JUCHU_STATUS_ORDERD;
                sqlParameter[2].Value = TrnJuchuh.JUCHU_STATUS_DELIVERD;
                sqlParameter[3].Value = searchDate.Year.ToString();
                sqlParameter[3].Value = searchDate.ToString("yyyy/MM/dd");

                SqlDataReader dataReader = db.GetDataCmd(sql, sqlParameter);
                if (dataReader.Read())
                {
                    total = (int)dataReader[0];
                }


                LogManager.info("TRN_Juchu_HPersister - result - total : " + total);

            }
            catch (Exception e)
            {
                LogManager.info("TRN_Juchu_HPersister - getTotalSeal : " + e.Message);
            }
            finally
            {
                db.Close();
            }

            return total;

        }

        /**
         * 受注No.を取得する。
         * @return String 生成された受注No.
        */
        public String makeJuchuNo()
        {
            String autoNum;
            String dateString;
            String juchu_no = null;
            int dateint = 0;

            try
            {
                dateString = System.DateTime.Now.ToString("yy");
                LogManager.info("TRN_Juchu_HPersiste - makJuchuNo - dateString :" + dateString);
                try
                {
                    dateint = int.Parse(dateString);
                }
                catch
                {
                    dateint = 1;
                }
                int number = getSequenceNextVal(dateString, "JUCHU_NO_SEQ");
                autoNum = string.Format("{0:0000000}", number);
                juchu_no = "J" + dateString + autoNum;

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Juchu_HPersister - getJuchu_no : " + e.Message);

            }

            return juchu_no;
        }

        /**
         * 回胴遊商様ユーザー用 受注見出更新メソッド
         * @param con コネクション
         * @param model データモデル
         * @return boolean
         * @throws PersisterSQLException
         */
        public bool updateJuchu_HForKaidouUser(SqlDB db, TrnJuchuh M)
        {

            String sql =
                "update trn_juchu_h set "
                    + "juchu_date = @juchu_date "
                    + " , union_code = @union_code "
                    + " , branch_code = @branch_code "
                    + " , juchu_tanto_name = @juchu_tanto_name "
                    + " , hope_date = @hope_date "
                    + " , hope_time_clss = @hope_time_clss "
                    + " , total_seal_qty = @total_seal_qty "
                    + " , ocr_order_qty = @ocr_order_qty "
                    + " , hachu_date = @hachu_date "
                    + " , hachu_union_code = @hachu_union_code "
                    + " , hachu_branch_code = @hachu_branch_code "
                    + " , deliv_date = @deliv_date "
                    + " , deliv_tanto_name = @deliv_tanto_name "
                    + " , deliv_comp_name = @deliv_comp_name "
                    + " , deliv_no = @deliv_no "
                    + " , fax_error_flg = @fax_error_flg "
                    + " , juchu_sts = @juchu_sts "
                    + " , upd_date = @upd_date "
                    + " , upd_union_code = @upd_union_code "
                    + " , upd_branch_code = @upd_branch_code "
                    + " , upd_func_id = @upd_func_id "
                    + " , upd_num = @upd_num "
                    + " where "
                    + "juchu_no = @juchu_no ";

            try
            {

                SqlParameter[] sqlParameter = {   
                                                    //new SqlParameter("@juchu_clss",SqlDbType.VarChar,2),
                                                    new SqlParameter("@juchu_date",SqlDbType.DateTime),
                                                    new SqlParameter("@union_code",SqlDbType.VarChar,5),
                                                    new SqlParameter("@branch_code",SqlDbType.SmallInt),
                                                    new SqlParameter("@juchu_tanto_name",SqlDbType.VarChar,20),
                                                    new SqlParameter("@hope_date",SqlDbType.DateTime),
                                                    new SqlParameter("@hope_time_clss",SqlDbType.VarChar,1),
                                                    new SqlParameter("@total_seal_qty",SqlDbType.Int),
                                                    new SqlParameter("@ocr_order_qty",SqlDbType.Int),
                                                    //new SqlParameter("@report_rate",SqlDbType.Real),
                                                    //new SqlParameter("@fax_rcv_date",SqlDbType.DateTime),
                                                    //new SqlParameter("@ocr_pass_txt",SqlDbType.VarChar,60),
                                                    new SqlParameter("@hachu_date",SqlDbType.DateTime),
                                                    new SqlParameter("@hachu_union_code",SqlDbType.VarChar,5),
                                                    new SqlParameter("@hachu_branch_code",SqlDbType.SmallInt),
                                                    new SqlParameter("@deliv_date",SqlDbType.DateTime),
                                                    new SqlParameter("@deliv_tanto_name",SqlDbType.VarChar,20),
                                                    new SqlParameter("@deliv_comp_name",SqlDbType.VarChar,30),
                                                    new SqlParameter("@deliv_no",SqlDbType.VarChar,15),
                                                    new SqlParameter("@fax_error_flg",SqlDbType.VarChar,1),
                                                    //new SqlParameter("@forced_juchu_flg",SqlDbType.VarChar,1),
                                                    new SqlParameter("@juchu_sts",SqlDbType.VarChar,2),
                                                    //new SqlParameter("@reg_date",SqlDbType.DateTime),
                                                    new SqlParameter("@upd_date",SqlDbType.VarChar,20),
                                                    new SqlParameter("@upd_union_code",SqlDbType.VarChar,5),
                                                    new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),
                                                    new SqlParameter("@upd_func_id",SqlDbType.VarChar,20),
                                                    new SqlParameter("@upd_num",SqlDbType.SmallInt),
                                                    new SqlParameter("@juchu_no",SqlDbType.VarChar,20)
                                                  };

                sqlParameter[0].Value = M.JuchuDate;
                sqlParameter[1].Value = M.UnionCode;
                sqlParameter[2].Value = M.BranchCode;
                sqlParameter[3].Value = M.JuchuTantoName;
               // sqlParameter[4].Value = M.HopeDate;
                if (M.HopeDate == null)
                {
                    sqlParameter[4].Value = DBNull.Value;
                }
                else
                {
                    sqlParameter[4].Value = M.HopeDate;
                }
                sqlParameter[5].Value = M.HopeTimeClss;
                sqlParameter[6].Value = M.TotalSealQty;
                sqlParameter[7].Value = M.OcrOrderQty;

                if (M.HachuDate == null)
                {
                     sqlParameter[8].Value  = DBNull.Value;
                }
                else
                {
                    sqlParameter[8].Value = M.HachuDate;
                }
                sqlParameter[9].Value = M.HachuUnionCode;
                sqlParameter[10].Value = M.HachuBranchCode;

                if (M.DelivDate == null)
                {
                    sqlParameter[11].Value = DBNull.Value;
                }
                else
                {
                    sqlParameter[11].Value = M.DelivDate;
                }
                if (StringUtil.isEmpty(M.DelivTantoName))
                {
                    sqlParameter[12].Value = "";
                }
                else
                {
                    sqlParameter[12].Value = M.DelivTantoName;
                }

                if (StringUtil.isEmpty(M.DelivCompName))
                {
                    sqlParameter[13].Value = "";
                }
                else
                {
                    sqlParameter[13].Value = M.DelivCompName;
                }

                if (StringUtil.isEmpty(M.DelivNo))
                {
                    sqlParameter[14].Value = "";
                }
                else
                {
                    sqlParameter[14].Value = M.DelivNo;
                }
                
                sqlParameter[15].Value = M.FaxErrorFlg;
                sqlParameter[16].Value = M.JuchuSts;
                sqlParameter[17].Value = M.UpdDate;
                sqlParameter[18].Value = M.UpdUnionCode;
                sqlParameter[19].Value = M.UpdBranchCode;
                sqlParameter[20].Value = M.UpdFuncId;
                sqlParameter[21].Value = M.UpdNum;
                sqlParameter[22].Value = M.JuchuNo;


                db.ExecuteCmd(sql, sqlParameter);
                //db.Close();


            }
            catch (Exception e)
            {
                LogManager.error("TRN_Juchu_HPersister - updateJuchu_HForKaidouUser:" + e.Message);
                return false;
            }

            return true;



        }
    }
}
